﻿Imports DTO
Imports System.Data.SqlClient

Public Class OrdersDAO
    Public Sub InsertOrders(ByVal receiptID As Integer, ByVal staffId As Integer, ByVal serviceID As Integer, ByVal quantity As Integer, ByVal roomId As Integer)
        Try
            Dim strSQL As String = String.Format("INSERT INTO Orders (ReceiptID, StaffID, ServiceID, Quantity, RoomId) VALUES({0}, {1}, {2}, {3}, {4})", receiptID, staffId, serviceID, quantity, roomId)
            Connection.ExcuteQuery(strSQL)
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Sub Update(ByVal receiptID As Integer, ByVal staffId As Integer, ByVal serviceID As Integer, ByVal quantity As Integer, ByVal roomId As Integer)
        Try
            Dim sSQL As String = "UPDATE Orders SET "
            sSQL = String.Format("{0} ServiceID={1}, Quantity={2}, RoomId={3} ", sSQL, serviceID, quantity, roomId)
            'sSQL = String.Format("{0} ", sSQL)
            'sSQL = String.Format("{0} ", sSQL)
            sSQL = String.Format("{0} Where ReceiptID={1} and staffId={2}", sSQL, receiptID, staffId)
            Connection.ExcuteQuery(sSQL)
            Connection.Dispose()
        Catch ex As Exception
            Connection.CheckCloseConnect()
            Throw ex
        End Try
    End Sub

    Public Function GetListOrdersByReceiptID(ByVal receiptID As Integer, ByVal staffId As Integer, ByVal roomId As Integer) As List(Of OrdersDTO)
        Dim listResult As New List(Of OrdersDTO)
        Try
            Dim sql As String = String.Format("SELECT ServiceID, Quantity  FROM Orders where ReceiptID={0} and staffId={1} and RoomId={2} order by ServiceID desc", receiptID, staffId, roomId)
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read)
                Dim ordersDto As New OrdersDTO
                ordersDto.ServiceID = reader("ServiceID")
                ordersDto.Quantity = reader("Quantity")
                listResult.Add(ordersDto)
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
        Return listResult
    End Function

    Public Sub DeleteByReceipId(ByVal receiptID As Integer, ByVal staffId As Integer, ByVal roomId As Integer)
        Try
            Dim sSQL As String = String.Format("DELETE FROM Orders WHERE ReceiptID={0} and staffId={1} and RoomId={2} ", receiptID, staffId, roomId)
            Connection.ExcuteQuery(sSQL)
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Function IsExistOrdersByReceiptID(ByVal receiptID As Integer, ByVal staffId As Integer, ByVal roomId As Integer) As List(Of OrdersDTO)
        Dim listResult As New List(Of OrdersDTO)
        Try
            Dim sql As String = String.Format("SELECT ServiceID, Quantity  FROM Orders where ReceiptID={0} and staffId={1} and RoomId={2} order by ServiceID desc", receiptID, staffId, roomId)
            Dim reader As SqlDataReader
            reader = Connection.ExcuteReader(sql)
            While (reader.Read)
                Dim ordersDto As New OrdersDTO
                ordersDto.ServiceID = reader("ServiceID")
                ordersDto.Quantity = reader("Quantity")
                listResult.Add(ordersDto)
            End While
            reader.Close()
            Connection.Dispose()
        Catch ex As Exception
            Throw ex
        End Try
        Return listResult
    End Function
End Class
